-- Task 10
USE tusharparmar;

--  Student Table
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL,
    objective TEXT NOT NULL,
    skills TEXT NOT NULL,
    education TEXT NOT NULL,
    experience TEXT NOT NULL,
    address TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--  Values table
CREATE TABLE IF NOT EXISTS values_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    student_name VARCHAR(150) NOT NULL,
    grade ENUM('A','B','C','D','F') NOT NULL,
    sgpa DECIMAL(3,2) NOT NULL,    -- Semester GPA (0.00 - 10.00)
    gpa DECIMAL(3,2) NOT NULL,     -- Overall GPA (0.00 - 10.00)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE
);

--  Reports Table 
USE tusharparmar;

CREATE TABLE IF NOT EXISTS reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    student_name VARCHAR(150) NOT NULL,
    Department VARCHAR(200) NOT NULL,
    course_name VARCHAR(150) NOT NULL,
    semester INT NOT NULL,
    report_title VARCHAR(255) NOT NULL,
    report_description TEXT NOT NULL,
    grade ENUM('A','B','C','D','F') DEFAULT 'F',
    sgpa DECIMAL(3,2),   -- Semester GPA at report time
    gpa DECIMAL(3,2),    -- Cumulative GPA
    report_status ENUM('Submitted','Under Review','Approved','Rejected') DEFAULT 'Submitted',
    examiner_name VARCHAR(150),
    examiner_comments TEXT,
    reviewed_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE
);


-- student_view
USE tusharparmar;
CREATE OR REPLACE VIEW student_view AS
SELECT 
    id AS student_id,
    name,
    email,
    phone,
    objective,
    skills,
    education,
    experience,
    address,
    created_at
FROM student;

-- values_view
CREATE OR REPLACE VIEW values_view AS
SELECT 
    v.id AS value_id,
    v.student_id,
    v.student_name,
    v.marks,
    v.grade,
    v.sgpa,
    v.gpa,
    v.created_at
FROM values_table v;

-- Reports_view
CREATE OR REPLACE VIEW reports_view AS
SELECT 
    r.id AS report_id,
    r.student_id,
    r.student_name,
    r.department,
    r.course_name,
    r.semester,
    r.report_title,
    r.report_description,
    r.grade,
    r.sgpa,
    r.gpa,
    r.report_status,
    r.examiner_name,
    r.examiner_comments,
    r.created_at,
    r.updated_at
FROM reports r;



